/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package com.mycompany.tianjia;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 *
 * @author qianhailong
 */
public class JdbcToH2 {
    
    //驱动类，H2jar包中可以找到
    public static final String DRIVER_CLASS = "org.h2.Driver";
    //数据库连接URL，当前连接的是E:/H2目录下的mydb数据库  只能一个连接，单点
    //  static final String JDBC_URL = "jdbc:h2:/Users/qianhailong/Downloads/aaaa/db/mydb";  可多个连接 
    public static final String JDBC_URL = "jdbc:h2:tcp://localhost/C:/Users/Administrator/test/chenfan.mv.db";
     //内存型
    //static final String JDBC_URL = "jdbc:h2:mem:mydb";
    //用户名
    public static final String USER = "knight";
    //密码
    public static final String PASSWORD = "865920295.";
    
    public static Connection conn = null;
    
    public static PreparedStatement stmt = null;
    
    public static ResultSet rs =null;
    
    
    public static Connection getConn() throws Exception{
           // 加载驱动
        Class.forName(DRIVER_CLASS);
        // 根据连接URL，用户名，密码获取数据库连接
        conn = DriverManager.getConnection(JDBC_URL, USER, PASSWORD);
        return conn;
    }
    
    
    public static int addStudent(String name,String sex,String age) throws Exception{
        conn = getConn();
        stmt = conn.prepareStatement("INSERT INTO student(name,sex,age) VALUES(?,?,?)");
         
        stmt.setString(1, name);
        stmt.setString(2, sex);
        stmt.setString(3, age);
        
        int i = stmt.executeUpdate();
        
        System.out.println("执行结果："+i);
        
        //关闭 
         close();
         
         return i;
        
    }
    
    public static List<Map<String,String>> findAll(String name) throws Exception{
         conn = getConn();
         if("".equals(name)){
              stmt = conn.prepareStatement("select * from student");
         }else{
             stmt = conn.prepareStatement("select * from student where name like %?%");
            stmt.setString(1, name);
         }
        
         rs = stmt.executeQuery();
         List<Map<String,String>> list = new ArrayList<Map<String,String>>();
         while (rs.next()) {
             Map<String,String> map = new HashMap<String,String>();
             map.put("id", rs.getString("id"));
             map.put("name", rs.getString("name"));
             map.put("sex", rs.getString("sex"));
             map.put("age", rs.getString("age"));
             list.add(map);
         }
         
         close();
         return list;
    }
    
    public static void updateStudent(int id) throws Exception{
//        conn = getConn();
//        stmt = conn.prepareStatement("update student set name='AAAA' where id =?");
//        
//        System.out.println("执行结果："+i);
//        close();
         
    }
    
    public static void delStudent(int id) throws Exception{
//        conn = getConn();
//        stmt = conn.createStatement();
//        int i = stmt.executeUpdate("delete from student where id ="+id);
//        System.out.println("执行结果："+i);
//        close();
    }
    
    //关闭
    public static void close(){
        try{
            if(null != rs){
               rs.close();
            }
            if(null != stmt){
                stmt.close();
            }
            
            if(null != conn){
                conn.close();
            }
        }
        catch(Exception e){
           e.printStackTrace();
        }
        
    }
    
    public static void main(String [] orgs){
        try{
            addStudent("1","1","1");
            
            //findAll();
            
           // updateStudent(1);
//            delStudent(1);
        }catch(Exception e){
            e.printStackTrace();
        }
        
    }
    
//     public static void jdbcDemo() throws Exception{
//       
//          // 加载驱动
//        Class.forName(DRIVER_CLASS);
//        // 根据连接URL，用户名，密码获取数据库连接
//        Connection conn = DriverManager.getConnection(JDBC_URL, USER, PASSWORD);
//        // 创建一个Statement 
//        Statement stmt = conn.createStatement();
//        
//        //如果存在表就先删除
//         stmt.execute("DROP TABLE IF EXISTS student");
//         
//         //创建student表
    //CREATE TABLE student(ID INT auto_increment  PRIMARY KEY,NAME VARCHAR(255),SEX VARCHAR(1),AGE INT);
//         stmt.execute("CREATE TABLE student(ID INT PRIMARY KEY,NAME VARCHAR(255),SEX VARCHAR(1),AGE INT)");
//        
//    }
    
    //CREATE TABLE student(ID INT auto_increment  PRIMARY KEY,NAME VARCHAR(255),SEX VARCHAR(10),AGE VARCHAR(10));
    
}
